package com.mine.site.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mine.site.model.Commodity;
import com.mine.site.model.ShoppingCart;
import com.mine.site.util.DbUtil;


public class CommodityDao {
	public List<Commodity> findAll1() {
		List<Commodity> Commo = new ArrayList<Commodity>();
		String sql = "select * from clnz  ";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Commodity u = new Commodity();
				u.setNumber(rs.getInt(1));
				u.setName(rs.getString(2));
				u.setTitle(rs.getString(3));
				u.setPrice(rs.getString(4));
				u.setUrl(rs.getString(5));
				u.setType(rs.getString(6));
				u.setImg_1(rs.getString(7));
				u.setImg_2(rs.getString(8));
				u.setImg_3(rs.getString(9));
				u.setImg_4(rs.getString(10));
				u.setMinute(rs.getString(11));
				Commo.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return Commo;
	}
	
	public List<Commodity> findAll() {
		List<Commodity> Commo = new ArrayList<Commodity>();
		String sql = "select * from clnz order by number DESC limit 10 ";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Commodity u = new Commodity();
				u.setNumber(rs.getInt(1));
				u.setName(rs.getString(2));
				u.setTitle(rs.getString(3));
				u.setPrice(rs.getString(4));
				u.setUrl(rs.getString(5));
				u.setType(rs.getString(6));
				u.setImg_1(rs.getString(7));
				u.setImg_2(rs.getString(8));
				u.setImg_3(rs.getString(9));
				u.setImg_4(rs.getString(10));
				u.setMinute(rs.getString(11));
				Commo.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return Commo;
	}
	
	public List<Commodity> findAll_all() {
		List<Commodity> Commo_all = new ArrayList<Commodity>();
		String sql = "select * from clnz order by number DESC ";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Commodity u = new Commodity();
				u.setNumber(rs.getInt(1));
				u.setName(rs.getString(2));
				u.setTitle(rs.getString(3));
				u.setPrice(rs.getString(4));
				u.setUrl(rs.getString(5));
				u.setType(rs.getString(6));
				u.setImg_1(rs.getString(7));
				u.setImg_2(rs.getString(8));
				u.setImg_3(rs.getString(9));
				u.setImg_4(rs.getString(10));
				u.setMinute(rs.getString(11));
				Commo_all.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return Commo_all;
	}
	
	public List<ShoppingCart> findAll_successAll() {
		List<ShoppingCart> Commo_successAll = new ArrayList<ShoppingCart>();
		String sql = "select * from success ";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ShoppingCart u = new ShoppingCart();
				u.setId(rs.getString(1));
				u.setName(rs.getString(2));
				u.setNumber(rs.getString(3));
				u.setTitle(rs.getString(4));
				u.setPrice(rs.getString(5));
				u.setUrl(rs.getString(6));
				Commo_successAll.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return Commo_successAll;
	}
	
		public List<Commodity> findAll_clvz() {
			List<Commodity> Commo_clnz = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='1' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_clnz.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_clnz;
		}
	
		public List<Commodity> findAll_lyq() {
			List<Commodity> Commo_lyq = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='2' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_lyq.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_lyq;
		}
		
		public List<Commodity> findAll_clnanz() {
			List<Commodity> Commo_clnanz = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='3' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_clnanz.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_clnanz;
		}
		
		public List<Commodity> findAll_nantx() {
			List<Commodity> Commo_nantx = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='1' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_nantx.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_nantx;
		}
		
		public List<Commodity> findAll_nvtx() {
			List<Commodity> Commo_nvtx = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='nvtx' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_nvtx.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_nvtx;
		}
		
		
		public List<Commodity> findAll_jpnk() {
			List<Commodity> Commo_jpnk = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='jpnk' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_jpnk.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_jpnk;
		}

		
		public List<Commodity> findAll_jonk() {
			List<Commodity> Commo_jonk = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='jonk' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
					Commo_jonk.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_jonk;
		}
		
		public List<Commodity> findAll_cjwt() {
			List<Commodity> Commo_cjwt = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='cjwt' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_cjwt.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_cjwt;
		}
		
		public List<Commodity> findAll_xlbx() {
			List<Commodity> Commo_xlbx = new ArrayList<Commodity>();
			String sql = "select * from clnz where type ='xlbx' limit 5";
			Statement stmt = null;
			try {
				stmt = DbUtil.getInstance().getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				while (rs.next()) {
					Commodity u = new Commodity();
					u.setNumber(rs.getInt(1));
					u.setName(rs.getString(2));
					u.setTitle(rs.getString(3));
					u.setPrice(rs.getString(4));
					u.setUrl(rs.getString(5));
					u.setType(rs.getString(6));
					u.setImg_1(rs.getString(7));
					u.setImg_2(rs.getString(8));
					u.setImg_3(rs.getString(9));
					u.setImg_4(rs.getString(10));
					u.setMinute(rs.getString(11));
			
					Commo_xlbx.add(u);
				}
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			return Commo_xlbx;
		}
		
	public List<Commodity> findByName(String Name) {
		List<Commodity> commo = new ArrayList<Commodity>();
		String sql = "select * from clnz where name like ? ";
		PreparedStatement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().prepareStatement(sql);
			stmt.setString(1, "%"+Name +"%");
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Commodity u = new Commodity();
				
				u.setNumber(rs.getInt(1));
				u.setName(rs.getString(2));
				u.setTitle(rs.getString(3));
				u.setPrice(rs.getString(4));
				u.setUrl(rs.getString(5));
				u.setType(rs.getString(6));
				u.setImg_1(rs.getString(7));
				u.setImg_2(rs.getString(8));
				u.setImg_3(rs.getString(9));
				u.setImg_4(rs.getString(10));
				u.setMinute(rs.getString(11));
				commo.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return commo;
	}
	
	
	
	
	public void save(Commodity u){
		String sql="insert into clnz values(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql);
			pst.setInt(1, u.getNumber());
			pst.setString(2, u.getName());
			pst.setString(3, u.getTitle());
			pst.setString(4, u.getPrice());
			pst.setString(5, u.getUrl());
			pst.setString(6, u.getType());
			pst.setString(7, u.getImg_1());
			pst.setString(8, u.getImg_2());
			pst.setString(9, u.getImg_3());
			pst.setString(10, u.getImg_4());
			pst.setString(11, u.getMinute());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void update(Commodity u){
		String sql_update = "update clnz set name=?, title=?,price=?,url=?,img_1=?,img_2=?,img_3=?,img_4=?,minute=?,type=? where number=?";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql_update);
			
			pst.setString(1, u.getName());
			pst.setString(2, u.getTitle());
			pst.setString(3, u.getPrice());
			pst.setString(4, u.getUrl());
			
			pst.setString(5, u.getImg_1());
			pst.setString(6, u.getImg_2());
			pst.setString(7, u.getImg_3());
			pst.setString(8, u.getImg_4());
			pst.setString(9, u.getMinute());
			pst.setString(10, u.getType());
			pst.setInt(11, u.getNumber());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	
	public void delete(String Number){
		String sql = "delete from clnz where number=?";
		PreparedStatement pst = null;
		try {
			pst = DbUtil.getInstance().getConn().prepareStatement(sql);
			pst.setString(1, Number);

			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				pst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	  public static void main(String[] args) {
          int i = (int)Math.random()*2;
          System.out.println(i);
     }
	  
	  public Commodity findByNumber(String Number){
			 
			 String sql = "select * from clnz where Number=?";
			 Commodity u =null;
				try {
					PreparedStatement pst = DbUtil.getInstance().getConn()
							.prepareStatement(sql);
					pst.setString(1, Number);
					ResultSet rs = pst.executeQuery();
					if (rs.next()) {
						u = new Commodity();
						u.setNumber(rs.getInt(1));
						u.setName(rs.getString(2));
						u.setTitle(rs.getString(3));
						u.setPrice(rs.getString(4));
						u.setUrl(rs.getString(5));
						u.setType(rs.getString(6));
						u.setImg_1(rs.getString(7));
						u.setImg_2(rs.getString(8));
						u.setImg_3(rs.getString(9));
						u.setImg_4(rs.getString(10));
						u.setMinute(rs.getString(11));
					
					}
					rs.close();
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return u;
		 }

}
